﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupRemoveOldBackupsAll]
#-- Purpose:		Calls spBackupRemoveOldBackups for all databases.
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupRemoveOldBackupsAll]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@db_name sysname
DECLARE		@default_bkp_dir nvarchar(4000), @sError nvarchar(4000)
DECLARE		@tFiles TABLE (physical_device_name nvarchar(4000))
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupRemoveOldBackupsAll'

-----------------------------------------------------------
---
--- Remove the backup files that are no longer needed to meet
---	the recovery window for current databases
---
-----------------------------------------------------------

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT		db.name
    FROM		master.sys.databases db
    JOIN		master.sys.master_files mf ON db.database_id = mf.database_id
				AND mf.type = 0
    GROUP BY	db.name
    ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC dbo.spBackupRemoveOldBackups @database_name = @db_name

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

-----------------------------------------------------------
---
--- Build the list of files that no longer have a database
---
-----------------------------------------------------------

--- Get the default backup directory
EXEC		dbo.spGetBackupDirectory	@database_name = NULL, 
										@backup_directory_name = @default_bkp_dir OUTPUT

--- Get the list of files to delete from the file system
DECLARE crs CURSOR FOR
	SELECT		t1.subdirectory_name
	FROM		dbo.GetSubdirectories(@default_bkp_dir, 1) t1
	LEFT JOIN	master.sys.databases d ON REPLACE(t1.subdirectory_name, @default_bkp_dir, '') = d.name
	WHERE		d.name is null
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	INSERT INTO @tFiles
		SELECT physical_device_name FROM dbo.GetFiles(@default_bkp_dir, 1)
									
	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

--- Delete Files
DECLARE crs CURSOR FOR SELECT physical_device_name FROM @tFiles
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Set the message and log it to the error log
	SELECT	@sError = 'Deleting backup file: ' + @default_bkp_dir
	EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError

	BEGIN TRY
		--- Delete the backup file
		EXEC	dbo.DeleteFile @filePath = @default_bkp_dir
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Failed to delete backup file: ' + @default_bkp_dir
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError
	END CATCH

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

-----------------------------------------------------------
---
--- Build the list of directories that no longer have 
---	databases and delete them.
---
-----------------------------------------------------------

--- Get the default backup directory
EXEC		dbo.spGetBackupDirectory	@database_name = NULL, 
										@backup_directory_name = @default_bkp_dir OUTPUT
DECLARE crs CURSOR FOR
	SELECT		t1.subdirectory_name
	FROM		dbo.GetSubdirectories(@default_bkp_dir, 1) t1
	LEFT JOIN	master.sys.databases d ON REPLACE(t1.subdirectory_name, @default_bkp_dir, '') = d.name
	WHERE		d.name is null
OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Set the message and log it to the error log
	SELECT	@sError = 'Deleting subdirectory of old database: ' + @default_bkp_dir
	EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError

	BEGIN TRY
		--- Delete the backup file
		EXEC	dbo.DeleteDirectory @directoryPath = @default_bkp_dir, @recursive = 1
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Failed to delete subdirectory of old database: ' + @default_bkp_dir
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError
	END CATCH

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF